package com.viking.util.database.query;


import java.sql.*;
import java.util.Objects;

public class QueryInformationDataBase1 {
        //获取数据库的所有患者数据
        public static String[][] PatientInformation(int start, int end) throws ClassNotFoundException, SQLException {
            //1.加载驱动
            Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
            String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
            String username="testing";
            String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
            Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象
            Statement statement = connection.createStatement();
//        5.执行SQL的对象去执行SQL，返回结果集
         /*   String sql = "SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation";
     */
            Statement stmt = connection.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            ResultSet resultSet= stmt.executeQuery("SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation");


           // ResultSet resultSet = statement.executeQuery(sql);
            //处理获取数据库的信息
            String [][]data = new String[end-start][7];
            int i = 0;
            //当前数据库最后的行数
            resultSet.last();

            System.out.println("----"+resultSet.getRow());
            if(end>resultSet.getRow()&&start>resultSet.getRow())
            {
                System.out.println(resultSet.getRow());
                return null;
            }
            if(start<=resultSet.getRow()&&end>=resultSet.getRow()) {

                resultSet.absolute(start);
                System.out.println(resultSet.getRow());
                while (resultSet.next() && resultSet.getRow() <= end)
                {

                    data[i][0] = resultSet.getString(1);
                    data[i][1] = resultSet.getString(2);
                    data[i][2] = resultSet.getString(3);
                    data[i][3] = resultSet.getString(4);
                    data[i][4] = resultSet.getString(5);
                    data[i][5] = resultSet.getString(6);
                    data[i][6] = resultSet.getString(7);
                    i++;
                }

                int a;
                for (a = end; a < end - resultSet.getRow(); a++) {
                    data[a][0] = null;
                    data[a][1] = null;
                    data[a][2] = null;
                    data[a][3] = null;
                    data[a][4] = null;
                    data[a][5] = null;
                    data[a][6] = null;
                }
                for (a = 0; a < end - start; a++) {
                    System.out.print(data[a][0] + "     ");
                    System.out.print(data[a][1] + "     ");
                    System.out.print(data[a][2] + "    ");
                    System.out.print(data[a][3] + "    ");
                    System.out.print(data[a][4] + "     ");
                    System.out.print(data[a][5] + "     ");
                    System.out.print(data[a][6] + "    ");
                    System.out.println("\n");
                }
                return data;
            }
            resultSet.absolute(start);

            while(resultSet.next()&&resultSet.getRow()<=end)
            {

                data[i][0] =resultSet.getString(1);
                data[i][1] =resultSet.getString(2);
                data[i][2] =resultSet.getString(3);
                data[i][3] =resultSet.getString(4);
                data[i][4] =resultSet.getString(5);
                data[i][5] =resultSet.getString(6);
                data[i][6] =resultSet.getString(7);
                i++;
            }
           // String[][] restData=new String[19][7];

            System.out.println(i);
            int a=0;
            /*for(i = start;i<=end;i++){

                restData[a][0]=data[i][0];
                restData[a][1]=data[i][1];
                restData[a][2]=data[i][2];
                restData[a][3]=data[i][3];
                restData[a][4]=data[i][4];
                restData[a][5]=data[i][5];
                restData[a][6]=data[i][6];
                a++;
            }
*/
            System.out.println("患者姓名    姓名  性别  年龄  入院时间    出院时间    状态");
            for( a=0;a<end-start;a++)
            {
                System.out.print(data[a][0]+"     ");
                System.out.print(data[a][1]+"     ");
                System.out.print(data[a][2]+"    ");
                System.out.print(data[a][3]+"    ");
                System.out.print(data[a][4]+"     ");
                System.out.print(data[a][5]+"     ");
                System.out.print(data[a][6]+"    ");
                System.out.println("\n");
            }

            resultSet.close();
            statement.close();
            connection.close();
            return data;
        }
        //获取数据库的行数
        public static int getMax() throws Exception{
            Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
            String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
            String username="testing";
            String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
            Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象
            Statement statement = connection.createStatement();
//        5.执行SQL的对象去执行SQL，返回结果集
         /*   String sql = "SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation";
     */
            Statement stmt = connection.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            ResultSet resultSet= stmt.executeQuery("SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation");
            resultSet.last();

            return resultSet.getRow();
        }


        //查询患者名字
    public static String[][] PatientId(String id1) throws Exception {
        Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
        String username="testing";
        String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
        Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象
        Statement statement = connection.createStatement();
//        5.执行SQL的对象去执行SQL，返回结果集
         /*   String sql = "SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation";
     */
        int id = Integer.parseInt(id1);
        Statement stmt = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet= stmt.executeQuery("SELECT" +
                "\t*\n" +
                "FROM\n" +
                "\tpatientinformation\n" +
                "WHERE\n" +
                "\tpatientinformation.`患者编号` = "+id);

        String [][]data = new String[getMax()][7];

        int i=0;
        while(resultSet.next())
        {
            System.out.println(resultSet.getString(1));
            data[i][0] =resultSet.getString(1);
            data[i][1] =resultSet.getString(2);
            data[i][2] =resultSet.getString(3);
            data[i][3] =resultSet.getString(4);
            data[i][4] =resultSet.getString(5);
            data[i][5] =resultSet.getString(6);
            data[i][6] =resultSet.getString(7);
            i++;
        }
        return data;
    }
    public static String[][] PatienName(String name)throws Exception{
        Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
        String username="testing";
        String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
        Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象
        Statement statement = connection.createStatement();
//        5.执行SQL的对象去执行SQL，返回结果集
         /*   String sql = "SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation";
     */
        Statement stmt = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet= stmt.executeQuery("SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\tpatientinformation\n" +
                "WHERE\n" +
                "\tpatientinformation.`患者名` = \"" +  name + "\"");

        String [][]data = new String[getMax()][7];
        int i=0;
        while(resultSet.next())
        {
            System.out.println(resultSet.getString(1));
            data[i][0] =resultSet.getString(1);
            data[i][1] =resultSet.getString(2);
            data[i][2] =resultSet.getString(3);
            data[i][3] =resultSet.getString(4);
            data[i][4] =resultSet.getString(5);
            data[i][5] =resultSet.getString(6);
            data[i][6] =resultSet.getString(7);
            i++;
        }
        return data;
    }

    public static String[][] inquire(String name,String id)throws Exception{
        Class.forName ("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://8.130.32.3:3306/testing?useUnicode=true&characterEncoding=utf-8";
        String username="testing";
        String password="Yiee4M4sHAaNXAMf";
//        3.连接成功，数据库对象 Connection
        Connection connection = DriverManager.getConnection(url,username,password);
//        4.执行SQL对象Statement，执行SQL的对象
        Statement statement = connection.createStatement();
//        5.执行SQL的对象去执行SQL，返回结果集
         /*   String sql = "SELECT\n" +
                    "\tpatientinformation.*\n" +
                    "FROM\n" +
                    "\tpatientinformation";
     */
        System.out.println("----"+id+"----");
        Statement stmt = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        String basicSql = "SELECT * FROM patientinformation WHERE ";

        if (!Objects.equals(name, "") && !Objects.equals(id, "")){
            basicSql += "patientinformation.`患者编号` = \"" + id + "\" AND patientinformation.`患者名` = \"" + name + "\"";
        } else if (!Objects.equals(name, "") && Objects.equals(id, "")) {
            basicSql += "patientinformation.`患者名` = \"" + name +"\"";
        } else if (!Objects.equals(id, "") && Objects.equals(name, "")) {
            basicSql += "patientinformation.`患者编号` = \"" + id + "\"";
        }


//        ResultSet resultSet= stmt.executeQuery("SELECT\n" +
//                "\t*\n" +
//                "FROM\n" +
//                "\tpatientinformation\n" +
//                "WHERE\n" +
//                "\tpatientinformation.`患者编号` = " +id+"or\n" +
//                "\tpatientinformation.`患者名` = "+name);
        ResultSet resultSet = stmt.executeQuery(basicSql);

                String [][]data = new String[getMax()][7];
        int i=0;
        while(resultSet.next())
        {
            System.out.println(resultSet.getString(1));
            System.out.println(resultSet.getString(2));
            data[i][0] =resultSet.getString(1);
            data[i][1] =resultSet.getString(2);
            data[i][2] =resultSet.getString(3);
            data[i][3] =resultSet.getString(4);
            data[i][4] =resultSet.getString(5);
            data[i][5] =resultSet.getString(6);
            data[i][6] =resultSet.getString(7);
            i++;
        }
        return data;
    }

}
